<?php

/**
 * SASE is a supervisor allocation system for examinations that 
 * enables to semi automate the allocation problem and save a lot 
 * of man hours in comparison with the prevailing system.
 * 
 * SASE is free software provided to the Faculty of Engineering, 
 * University of Peradeniya. This software is not redistributable 
 * without authors consent.
 * 
 * SASE is provided free of charge to the Faculty of Engineering 
 * hoping it would be used to modernize the functionality of itself 
 * by adopting new technology. This software comes with absolutely 
 * NO WARRANTY and the publishers may not be liable to any damages 
 * occurred due to issues regarding this software.
 * 
 */

/*
	This is the main page of the admin section. It will load 
	appropriate pages according to the 'action' requred by the
	user.
	
	If user is not logged in, session variable 'admin_id' which
	will contain the admin id (in this case the email) not included,
	the user will be prompted to sign in. That page too will direct
	to this page.
*/

session_start();

include_once '../classes/database.php';
include_once '../classes/supervisor.php';
include_once '../classes/subject.php';
include_once '../classes/site.php';
include_once '../classes/tools.php';

$admin_id = $_SESSION['admin_id'];

include 'config.php';
$connection = mysql_connect($db_host , $db_username, $db_password);
$db = mysql_select_db($db_name);

//include 'newfile.php';
if ($admin_id){ 

	if ($_GET['action']) {
		
		switch($_GET['action']){
			
			case 'logout' :	//will destroy the session
				logout();
				break;
				
			case 'add_timetable': //Will give the admin a subject by subject add facility
				break;
				
			case 'add_excel': //Will give the admin the capability to upload an XLS
				include 'upload.page.php';
				break;
                
			case 'add_subject':
				//Shows the add subject page
				include 'add_subject.page.php';
				break;

			case 'add_subject_process':
				//Processes the information obtained by the submission of the add subject page
				add_subject_process($_GET['sub_name'], $_GET['sub_code'], $_GET['sub_supervisors'], 
					$_GET['sub_stdcount'], $_GET['sub_day'], $_GET['sub_start'], $_GET['sub_end'], $_GET['sub_available']);
					
				//Look to which page to load next
				if ($_GET['submit']) {
					//User needed to add one subject only
					include 'index.page.php';
				}
				elseif ($_GET['submit_more']) {
					//User needs to add moer subjects, so bring back to same page
					include 'add_subject.page.php';	
				}
				break;
				
			case 'add_supervisor':
				//This method will show the add supervisor pae to the user
				include 'add_supervisor.page.php';
				break;
				
			case 'add_supervisor_process':
				add_supervisor_process($_GET['sup_name'], $_GET['sup_id'], $_GET['sup_subjects'], $_GET['sup_available']);
				
				if ($_GET['sup_submit']) {
					
				}
				elseif ($_GET['sup_submit_more']) {
					include 'add_supervisor.page.php';
				}
				
				break;
				
			case 'edit_timetable':
				break;
				
			case 'message':
				//Message
				include 'message.page.php';
				break;
				
			case 'send_message':
				//echo 'reached here';
				send_message($from, $to, $subject, $message);
				break;
				
			case 'soft_reset':
				echo 'soft resetting';
				soft_reset();
				break;
			
			case 'hard_reset':
				echo 'hard resetting';
				hard_reset();
				
			case 'settings':
				include 'settings.page.php';
				break;
                
            //Handle action related to settings;
            
            case 'Update EMail Detail':
                if ( $_GET['mail_pwd'] == $_GET['mail_pwd_confirm'] ) 
					Site::setMailDetail($_GET['mail_email'],$_GET['mail_pwd'], $_GET['mail_smtp']);
                include 'settings.page.php';
                break;
			
            case 'Update Sending Options':
                //create input array
                //echo 'here';
                Site::setMailSendingOptions($_GET['chk_on_signup'], $_GET['chk_on_enrolment'], $_GET['chk_on_drop'], $_GET['chk_on_complete']);
				include 'settings.page.php';
                break;
            
			case 'Update Basic Detail':
                //echo 'here';
				Site::setSiteBasics($_GET['std_sup_ratio'], $_GET['sup_alloc_min']);
                include 'settings.page.php';
				break;
				
			case 'subject_page':
                //Hanlde user input
                
                if ($_GET['delete']) {
                    Subject::deleteSubject($_GET['sub_code']);
                }
                
                if ($_GET['update']) {
                    //Create arrays
					$subDetails = array('sub_name' => $_GET['sub_name'], 'sub_stdcount' => $_GET['sub_stdcount'], 'sub_day'=> $_GET['sub_day'], 'sub_start' => $_GET['sub_start'], 'sub_end' => $_GET['sub_end'], 'sub_available' => $_GET['sub_available'], 'sub_hall' => $_GET['sub_hall']);
					
					$subDefaultSupervisors = explode(',', $_GET['sub_default_supervisors']);
					$subDefaultSupervisors = Tools::RemoveArraySpaces($subDefaultSupervisors);
					
					Subject::updateSubject($_GET['sub_code'], $subDetails, $subDefaultSupervisors);

                }
                
				include 'subject.page.php';
				break;
			
			case 'supervisor_page':
            
                //Hanlde user input
                
                if ($_GET['delete']) {
                    Supervisor::deleteSupervisor($_GET['sup_id']);
                }
                
                if ($_GET['update']) {
                    //Create arrays
					$supDetails = array('sup_name' => $_GET['sub_name'], 'sup_department' => $_GET['sup_department'], 
						'sub_available' => $_GET['sub_available']);

					//$subDefaultSupervisors = Tools::RemoveArraySpaces($subDefaultSupervisors);
					Supervisor::updateSupervisor($_GET['sup_id'],$supDetails );

                }
                
				include 'supervisor.page.php';
				break;
			
			default :
				include 'index.page.php';
				break;
		}
	}
	elseif ($_POST['action']) {
		//echo 'post action';
		switch ($_POST['action']) {
			case 'upload_timetable_xls':
                include 'excel_analyser.class.php';
                
				$path = upload_file('upload_file', 'upload', 'time_table.xlsx');
				$data = read_xls_to_array($path);
				print_r($data);
				break;

			case 'upload_xls':
				//echo 'hi';
                include 'excel_analyser.class.php';
                $path = upload_file('upload_file', 'upload', 'details.xlsx');
                $processor = new ExcelAnalyser($path);
                //Delete every thing but the site basic details
                hard_reset();
                //include 'index.page.php';
                //update the 3 databases
                $processor->process();
				echo 'here';
                $conflicts = $processor->get_inconsistancy();
                
                echo 'conflicts ' . printr($conflicts);
                
                break;
		}
	}
	else {
		//Just show the index page	
		include 'index.page.php';
	}
}

//User is not logged in
else{

	if ($_POST['action']=='login_process') {
		login($_POST['login_username'], $_POST['login_password']);
	}
	else {
		include 'login.page.php';	
	}
}

//Closes the open mysql connection
mysql_close($connection);


/**
 * 
 * Enter description here ...
 * @param String $sup_name Name of the supervisor
 * @param String $sup_id Email (ID) of the supervisor
 * @param CSV-String $sup_subs Subject codes handled by the supervisor by default
 * @param 1/0 $sup_available 1 : available 0 : not available
 */
function add_supervisor_process($sup_name, $sup_id, $sup_subs, $sup_available) {
	global $email_address;
	
	//Generate a password
	$password = generate_password();
	
	//Insert into database
	$query = "insert into sase_supervisor values ('$sup_id', '$sup_name', '$password', $sup_available)";
	$result = mysql_query($query);

	if ($result) {
		$subject = 'SASE - Login Credentials';
		$message = 'Welcome ' . $sup_name . '!' . PHP_EOL . PHP_EOL
					. 'You have been added as an supervisor for the Supervisor '
					. 'Allocation System for Examinations. ' . PHP_EOL . PHP_EOL
					. 'Username : ' . $sup_id . PHP_EOL
					. 'Password : ' . $password . PHP_EOL . PHP_EOL
					. 'Please login and confirm its usability' . PHP_EOL . PHP_EOL
					. 'Thank you'; 
		//send message to supervisor saying the new password
		send_message($email_address, $sup_id, $subject, $message);
	}
}

/**
 * This method will generate a password of a desired length and strength
 * If parameters are not passed, default length is 9 haracters and strength
 * is level 0
 * @param Int $length Lenth of the password in characters
 * @param Int $strength Level of strength of password
 */
function generate_password($length=9, $strength=0) {
	$vowels = 'aeuy';
	$consonants = 'bdghjmnpqrstvz';
	if ($strength & 1) {
		$consonants .= 'BDGHJLMNPQRSTVWXZ';
	}
	if ($strength & 2) {
		$vowels .= "AEUY";
	}
	if ($strength & 4) {
		$consonants .= '23456789';
	}
	if ($strength & 8) {
		$consonants .= '@#$%';
	}
 
	$password = '';
	$alt = time() % 2;
	for ($i = 0; $i < $length; $i++) {
		if ($alt == 1) {
			$password .= $consonants[(rand() % strlen($consonants))];
			$alt = 0;
		} else {
			$password .= $vowels[(rand() % strlen($vowels))];
			$alt = 1;
		}
	}
	return $password;
}

/**
 * 
 * Enter description here ...
 * @param String $sub_name Name of the subject
 * @param String $sub_code Subject code : CO314
 * @param String $sub_supervisors Coma seperated list of supervisor ids
 * @param Int $sub_stdcount Number of students sitting the exam
 * @param String $sub_day Exam day
 * @param String $sub_start Start time of exam
 * @param String $sub_end End time of exam
 * @param Int $sub_available If the subject is available or not, if not held set to '0'
 */
function add_subject_process($sub_name, $sub_code, $sub_supervisors, $sub_stdcount, $sub_day, $sub_start, $sub_end, $sub_available) {
    //Generating supervisors for the subject from coma seperated input string
   	$sup_arr = explode(',', $sub_supervisors);
    	
    //remove spaces in the supervisor array
    for ($i = 0; $i < count($sup_arr); $i++) {
        $sup_arr[$i] = trim($sup_arr[$i]);
   	}
    
        Subject::addSubject($sub_code, $sub_name, $sub_stdcount, $sub_day, $sub_start, $sub_end, $sub_available, $sup_arr);
}


/**
 * 
 * send_message will take smtp protocol settings, source email and
 * destination email as an argument alongside the message body. Then it
 * will attempt to send a message to the desired recepiants.
 * 
 * @param String $from Email address of the 
 * @param String $to Email address list of the recepiants
 * @param String $subject Subject of the message
 * @param String $message The body of the message
 */
function send_message($from, $to, $subject, $message){
	//The most basic implementation of the mail function
	mail($to, $subject, $message);
}



function add_exam_excel_processfile(){
	
}

/**
 * This method will upload a file to the servers desired folder 
 * and processes it.
 * @param String $upload_name Is the name given for the upload, 
 * for example $_FILES['upload_name'] retreval of file and information
 * @param String $folder_name path of the folder that the file needs to be stored
 * @param String $file_name is the name that is to be given to the file
 */
function upload_file($upload_name, $folder_name, $file_name){
	/**lets make the path of the new file, it needs to be checked if it
	 * has a slash at the end, but for now lets assume that it doesn't have.
	 */ 
	$path = $folder_name . '/' . $file_name;
	echo '<br>Path : ' . $path . '<br>';
	
	if ($_FILES['upload_file'] && !empty($_FILES['upload_file']['name'])){
		
		//check if the file is of Microsoft Excel (xls) format
		$tmp_split = explode('.', $_FILES['upload_file']['name']);
		print_r($tmp_split);
		if ($tmp_split[count($tmp_split) - 1] == 'xlsx')
		{	
			if(copy($_FILES['upload_file']['tmp_name'], $path)) {
				return $path;
			}
			else {
				echo 'error when copying';
				return false;
			}	
		}
		else {
			echo 'The upload should be a Microsoft Excel XLSX - .xlsx file';
		}
		
	}
	else {
		echo 'there has been an error in uploading the file';
		return false;
	}
}


function read_xls_to_array($file_path) {
	$data = array();
	echo 'here';
	include_once 'classes/reader.php';
	echo 'here';
	$ss = new Spreadsheet_Excel_Reader();
	echo 'here';
	$ss->setOutputEncoding('CP1251');
	$ss->read('C:\\inetpub\\wwwroot\\sase\\admin\\upload\\time_table.xls');
	echo 'num rows'.$ss->sheets[0]['numRows'];
	error_reporting(E_ALL ^ E_NOTICE);
	for ($i = 1; $i <= $ss->sheets[0]['numRows']; $i++) {
		echo 'reading';
		for ($j = 1; $j <= $ss->sheets[0]['numCols']; $j++) {
			$data[$i][$j] = $ss->sheets[0]['cells'][$i][$j];
		}
	}
	return $data;
}


/**
 * This method will delete a file, specified by the relevent path.
 * @param String $path Path of the file
 */
function delete_file($path){
}


/**
 * This method will end the present session, thus would logout 
 * the current user
 */
function logout(){
	session_destroy();
	include 'login.page.php';
}

/**
 * Connects to the database and checks if the provided username and 
 * password are correct. If correct would make an session variable
 * by the identified name.
 * 
 * @param string $username The username provided by the user
 * @param string $password The password provided by the user
 * @param string $includer The file that needs to be included if the login is successful
 */
function login($username, $password){
	$dbo = Database::getDBOInstance();
	$query = "select * from sase_admin where admin_id = '$username' and admin_pwd = '$password'";
	//echo $query;
	$result = $dbo->mysql_query($query);
	if (mysql_num_rows($result) == 1){
		$_SESSION['admin_id'] = $username;
		include 'index.page.php';
		
	}
	else{
		//reinclude signin page
		include 'login.page.php';
	}
}

/**
 * This method will reset the database to its basic position. Supervisors
 * and Subjects would not be removed by this reset. Those that are affected 
 * are 'supervises_subject', 'supervisor_sub_count' and 'subject_sup_count'.
 * 
 * default_supervisor table will be used to reset these effected tables
 */
function soft_reset() {
	//Clear supervisor_sub_count, subject_sup_count
	$query = "select distinct sup_id from supervisor_sub_count";
	$result = mysql_query($query);
	
	while ($row = mysql_fetch_assoc($result)) {
		$query = "delete from supervisor_sub_count where sup_id = '" . $row['sup_id'] . "'";
	}
	
	
	//Clear supervisor_sub_count, subject_sup_count
	$query = "select distinct sub_code from subject_sup_count";
	$result = mysql_query($query);
	
	while ($row = mysql_fetch_assoc($result)) {
		$query = "delete from subject_sup_count where sup_id = '" . $row['sub_code'] . "'";
	}
	
	//By now the two tables should be empty, lets clear the supervises_exam table used as a relationship
	
	//Get all the unique subject codes
	$query = "select distinct sub_code from supervises_subject";;
	$result = mysql_query($query);
	
	$sub_list = array();
	
	while ($row = mysql_fetch_assoc($result)) {
		$sub_list[] = $row['sub_code'];
	}
	
	//Now lets delete them form the table
	foreach ($sub_list as $sub) {
		$quey = "delete from supervises_subject where sub_code = '$sub_code'";
		$result = mysql_query($query);
	}
	
	//Make 2 arrays to hold the count of subjects per supervisor, and count of supervisors per subject
	$arr_supervisor_sub_count = array();
	$arr_subject_sup_count = array();
	
	//get the unique supervisors
	$query = "select distinct sup_id from default_supervisors";
	$result = mysql_query($query);
	
	while ($row = mysql_fetch_assoc ($result)) {
		$arr_supervisor_sub_count[$row['sup_id']] = 0;
	}
	
	//Get unique subjects
	$query = "select distinct sub_code from default_supervisors";
	$result = mysql_query($query);
	
	while ($row = mysql_fetch_assoc($result)) {
		$arr_subject_sup_count[$row['sub_code']] = 0;
	}
	
	//Adding default supervisors to the tables supervises_exam: at first default supervises are by default selected
	$query = "select * from default_supervisor";
	$result = mysql_query($query);
	
	//Lets make 1 query string, for the supervises_exam, and 2 counts for supervisor_sub_count and subject-sup_count
	//Thes counts are arrays
	
	$sql_supervises_subject = "insert into supervises_exam values ";
	
	while ($row = mysql_fetch_assoc($result)){
		//Add things to supervises_subject
		$sql_supervises_subject = $sql_supervises_subject . "('" . $row['sub_code'] . "' , '" . $row['sup_id'] . "') , ";

		//Update the counts
		$arr_subject_sup_count[$row['sub_code']] = $arr_subject_sup_count[$row['sub_code']] + 1;
		$arr_supervisor_sub_count[$row['sup_id']] = $arr_supervisor_sub_count[$row['sup_id']] + 1; 
	}
	
	//remove the last comma from the query string
	$sql_supervises_subject = substr($sql_supervises_subject, 0, strlen($sql_supervises_subject) - 3);
	
	echo $sql_supervises_subject;
	
	//make query string to update the other 2 tables using the cont
	$sql_supervisor_sub_count = "insert into supervisor_sub_count values ";
	$sql_subject_sup_count = "insert into subject_sup_count ";
	
	foreach ($arr_supervisor_sub_count as $key => $value) {
		$sql_supervisor_sub_count = $sql_supervisor_sub_count . "( '" . $key . "' , " . $value . " ) , ";
	}
	
	foreach ($arr_subject_sup_count as $key => $value) {
		$sql_subject_sup_count = $sql_subject_sup_count . "( '" . $key . "' , " . $value . " ) , ";
	}
	
	//Remove the comma from the two query strings
	$sql_supervisor_sub_count = substr($sql_supervisor_sub_count, 0, strlen($sql_supervisor_sub_count) - 3);
	$sql_subject_sup_count = substr($sql_subject_sup_count, 0, strlen($sql_subject_sup_count) - 3);
	
	echo $sql_subject_sup_count . '<br>';
	echo $sql_supervisor_sub_count . '<br>';
}

function hard_reset() {
	//Clear all subjects
	$query = "DELETE FROM sase_subject";
	$result = mysql_query($query);
	echo $query;
	//Clear default supervises
	$query = "DELETE FROM default_supervisor";
	$result = mysql_query($query);
	
	//Clear all supervisors
	$query = "DELETE FROM sase_supervisor";
	$result = mysql_query($query);
	
	//Reset counts
	$query = "DELETE FROM supervisor_sub_count";
	$result = mysql_query($query);
	$query = "DELETE FROM subject_sup_count";
	$result = mysql_query($query);
	
	//Reset supervises_subject relation
	$query = "DELETE FROM supervises_subject";
	$result = mysql_query($query);
	echo 'hard resetted';
}
?>